Darpa Anireddy

In [91]:
import pandas as pd
import numpy as np
import plotly.graph_objs as go
import plotly.express as px

  File "/var/folders/89/gtmbmmjd5kj5mpkf26_ykqzm0000gn/T/ipykernel_3346/1287185021.py", line 3
    pip install plotly
        ^
SyntaxError: invalid syntax
In [ ]:
# Question 1
# Display this subject's data as a Sankey diagram. 
# Display as many levels as you can (at least 3) for Type = 1, starting from the intracranial volume.
In [74]:
#read in data
## load in the hierarchy information
url = "https://raw.githubusercontent.com/bcaffo/MRIcloudT1volumetrics/master/inst/extdata/multilevel_lookup_table.txt"
multilevel_lookup = pd.read_csv(url, sep = "\t").drop(['Level5'], axis = 1)
multilevel_lookup = multilevel_lookup.rename(columns = {
    "modify"   : "roi", 
    "modify.1" : "level4",
    "modify.2" : "level3", 
    "modify.3" : "level2",
    "modify.4" : "level1"})
multilevel_lookup = multilevel_lookup[['roi', 'level4', 'level3', 'level2', 'level1']]
multilevel_lookup.head()

id=127
## load in the hierarchy information
subjectData = pd.read_csv("https://raw.githubusercontent.com/bcaffo/ds4bme_intro/master/data/kirby21.csv")
subjectData = subjectData.loc[(subjectData.type == 1) & (subjectData.level == 5) & (subjectData.id == id)]
subjectData = subjectData[['roi', 'volume']]
## Merge the subject data with the multilevel data
subjectData = pd.merge(subjectData, multilevel_lookup, on = "roi")
subjectData = subjectData.assign(icv = "ICV")
subjectData = subjectData.assign(comp = subjectData.volume / np.sum(subjectData.volume)
                                

  File "/var/folders/89/gtmbmmjd5kj5mpkf26_ykqzm0000gn/T/ipykernel_3346/3008920863.py", line 23
    
    ^
SyntaxError: unexpected EOF while parsing
In [75]:
fig = px.sunburst(subjectData, path=['icv', 'level1', 'level2', 'level3', 'level4', 'roi'], 
                  values='comp', width=800, height=800)
fig.show()
In [69]:
label = ["0", "1", "2", "3", "4", "level4", "level3", "level2", "level1", "icv"]
source = [0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4]
target = [5, 6, 7, 8, 9, 5, 6, 7, 8, 9, 5, 6, 7, 8, 9, 5, 6, 7, 8, 9, 5, 6, 7, 8, 9]
value = ['SFG_L', 'Frontal_L', 'CerebralCortex_L', 'Telencephalon_L', 'ICV', 'SFG_R', 'Frontal_R', 'CerebralCortex_R', 'Telencephalon_R', 'ICV', 'SFG_L', 'CerebralCortex_L', 'Telencephalon_L', 'ICV', 'SFG_R','Frontal_R','CerebralCortex_R','Telencephalon_R','ICV','SFG_L', 'Frontal_L', 'CerebralCortex_L', 'Telencephalon_L', 'ICV']
link = dict(source = source, target = target, value = value)
node = dict(label = label, pad = 35, thickness = 10)
data = go.Sankey(link = link, node = node)

fig.update_layout(
    title = "Kirby21 Dataset Sankey Diagram",
    font_size = 16,
    height = 800,
    width = 1200,
    font = dict(size = 10, color = 'white'))

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
/var/folders/89/gtmbmmjd5kj5mpkf26_ykqzm0000gn/T/ipykernel_3346/624375577.py in <module>
      7 data = go.Sankey(link = link, node = node)
      8 
----> 9 fig.update_layout(
     10     title = "Kirby21 Dataset Sankey Diagram",
     11     font_size = 16,

NameError: name 'fig' is not defined
In [ ]:
#Create a simple webpage containing this graphic and host it on github pages. 
#-Do not- host this off of your assignment repo from github classroom, since this is not public. 
# Instead, you'll have to create a new public repo from your regular github account and add this file. 
# Put the link to your live web page in a markdown cell of your hw4.ipynb file. 
#Note, an easy way to create a webpage with this graphic is to export an ipynb as an html file.
In [83]:
import sqlite3 as sq3

# create a SQLite database
con = sq3.connect('/Users/darpaanireddy/opioid.db')

# write the dataframes to the SQLite database
annual = pd.read_sql_query("SELECT * from annual", con)
land = pd.read_sql_query("SELECT * from land", con)
population = pd.read_sql_query("SELECT * from population", con)

print(annual)
print(land)
print(population)

# close the connection
conn.close()

annual.loc[(annual.BUYER_STATE == "AR") & (annual.BUYER_COUNTY == "MONTGOMERY"), "countyfips"] = "05097"
annual = annual[annual.BUYER_COUNTY.str.contains("NA")==False]

land_area = land[["Areaname", "STCOU", "LND110210D"]]
land_area = land_area.rename(columns={"STCOU": "countyfips"})

county_info = population.merge(land_area, how = "left", on = "countyfips")
county_info

             BUYER_COUNTY BUYER_STATE  year count DOSAGE_UNIT countyfips
0          1    ABBEVILLE          SC  2006   877      363620      45001
1          2    ABBEVILLE          SC  2007   908      402940      45001
2          3    ABBEVILLE          SC  2008   871      424590      45001
3          4    ABBEVILLE          SC  2009   930      467230      45001
4          5    ABBEVILLE          SC  2010  1197      539280      45001
...      ...          ...         ...   ...   ...         ...        ...
27753  27754           NA          NV  2007   447      200600         NA
27754  27755           NA          NV  2008     5        2200         NA
27755  27756           NA          OH  2006    23        5100         NA
27756  27757           NA          PR  2006    10       17800         NA
27757  27758           NA          PR  2007     2        1300         NA

[27758 rows x 7 columns]
         ?        Areaname  STCOU LND010190F  LND010190D LND010190N1  \
0        1   UNITED STATES  00000          0  3787425.08        0000   
1        2         ALABAMA  01000          0    52422.94        0000   
2        3     Autauga, AL  01001          0      604.49        0000   
3        4     Baldwin, AL  01003          0     2027.08        0000   
4        5     Barbour, AL  01005          0      904.59        0000   
...    ...             ...    ...        ...         ...         ...   
6392  3194  Sweetwater, WY  56037          0    10491.73        0000   
6393  3195       Teton, WY  56039          0     4221.96        0000   
6394  3196       Uinta, WY  56041          0     2087.66        0000   
6395  3197    Washakie, WY  56043          0     2242.85        0000   
6396  3198      Weston, WY  56045          0     2400.13        0000   

     LND010190N2 LND010200F  LND010200D LND010200N1  ... LND110210N1  \
0           0000          0  3794083.06        0000  ...        0000   
1           0000          0    52419.02        0000  ...        0000   
2           0000          0      604.45        0000  ...        0000   
3           0000          0     2026.93        0000  ...        0000   
4           0000          0      904.52        0000  ...        0000   
...          ...        ...         ...         ...  ...         ...   
6392        0000          0    10491.17        0000  ...        0000   
6393        0000          0      4221.8        0000  ...        0000   
6394        0000          0     2087.56        0000  ...        0000   
6395        0000          0     2242.75        0000  ...        0000   
6396        0000          0     2400.07        0000  ...        0000   

     LND110210N2 LND210190F LND210190D LND210190N1 LND210190N2 LND210200F  \
0           0000          0  251083.35        0000        0000          0   
1           0000          0    1672.71        0000        0000          0   
2           0000          0       8.48        0000        0000          0   
3           0000          0     430.55        0000        0000          0   
4           0000          0      19.59        0000        0000          0   
...          ...        ...        ...         ...         ...        ...   
6392        0000          0      65.86        0000        0000          0   
6393        0000          0     214.02        0000        0000          0   
6394        0000          0       5.91        0000        0000          0   
6395        0000          0       2.69        0000        0000          0   
6396        0000          0       2.21        0000        0000          0   

     LND210200D LND210200N1 LND210200N2  
0     256644.62        0000        0000  
1       1675.01        0000        0000  
2          8.48        0000        0000  
3        430.58        0000        0000  
4         19.61        0000        0000  
...         ...         ...         ...  
6392      65.87        0000        0000  
6393     214.04        0000        0000  
6394        5.9        0000        0000  
6395       2.69        0000        0000  
6396       2.21        0000        0000  

[6397 rows x 35 columns]
           ?   BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY  \
0          1        AUTAUGA          AL      01001     1      1   
1          2        BALDWIN          AL      01003     1      3   
2          3        BARBOUR          AL      01005     1      5   
3          4           BIBB          AL      01007     1      7   
4          5         BLOUNT          AL      01009     1      9   
...      ...            ...         ...        ...   ...    ...   
28260  28261       WASHAKIE          WY      56043    56     43   
28261  28262         WESTON          WY      56045    56     45   
28262  28263        SKAGWAY          AK      02230     2    230   
28263  28264  HOONAH ANGOON          AK      02105     2    105   
28264  28265     PETERSBURG          AK      02195     2    195   

         county_name                               NAME    variable  year  \
0            Autauga            Autauga County, Alabama  B01003_001  2006   
1            Baldwin            Baldwin County, Alabama  B01003_001  2006   
2            Barbour            Barbour County, Alabama  B01003_001  2006   
3               Bibb               Bibb County, Alabama  B01003_001  2006   
4             Blount             Blount County, Alabama  B01003_001  2006   
...              ...                                ...         ...   ...   
28260       Washakie           Washakie County, Wyoming  B01003_001  2014   
28261         Weston             Weston County, Wyoming  B01003_001  2014   
28262        Skagway       Skagway Municipality, Alaska  B01003_001  2014   
28263  Hoonah Angoon  Hoonah-Angoon Census Area, Alaska  B01003_001  2014   
28264     Petersburg         Petersburg Borough, Alaska  B01003_001  2014   

      population  
0          51328  
1         168121  
2          27861  
3          22099  
4          55485  
...          ...  
28260       8444  
28261       7135  
28262        996  
28263       2126  
28264       3212  

[28265 rows x 11 columns]
Out[83]:
? BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY county_name NAME variable year population Areaname LND110210D
0 1 AUTAUGA AL 01001 1 1 Autauga Autauga County, Alabama B01003_001 2006 51328 Autauga, AL 594.44
1 1 AUTAUGA AL 01001 1 1 Autauga Autauga County, Alabama B01003_001 2006 51328 Autauga, AL 594.44
2 2 BALDWIN AL 01003 1 3 Baldwin Baldwin County, Alabama B01003_001 2006 168121 Baldwin, AL 1589.78
3 2 BALDWIN AL 01003 1 3 Baldwin Baldwin County, Alabama B01003_001 2006 168121 Baldwin, AL 1589.78
4 3 BARBOUR AL 01005 1 5 Barbour Barbour County, Alabama B01003_001 2006 27861 Barbour, AL 884.88
... ... ... ... ... ... ... ... ... ... ... ... ... ...
56516 28263 SKAGWAY AK 02230 2 230 Skagway Skagway Municipality, Alaska B01003_001 2014 996 Skagway, AK 452.33
56517 28264 HOONAH ANGOON AK 02105 2 105 Hoonah Angoon Hoonah-Angoon Census Area, Alaska B01003_001 2014 2126 Hoonah-Angoon, AK 7524.92
56518 28264 HOONAH ANGOON AK 02105 2 105 Hoonah Angoon Hoonah-Angoon Census Area, Alaska B01003_001 2014 2126 Hoonah-Angoon, AK 7524.92
56519 28265 PETERSBURG AK 02195 2 195 Petersburg Petersburg Borough, Alaska B01003_001 2014 3212 Petersburg, AK 3281.98
56520 28265 PETERSBURG AK 02195 2 195 Petersburg Petersburg Borough, Alaska B01003_001 2014 3212 Petersburg, AK 3281.98

56521 rows × 13 columns

In [ ]:
#Create an interactive scatter plot of average number of opiod pills by year plot using plotly. 
#See the example here. Don't do the intervals (little vertical lines), only the points. 
#Add your plot to an html file with your repo for your Sanky diagram and host it publicly. 
#Put a link to your hosted file in a markdown cell of your hw4.ipynb file. 
#Note, an easy way to create a webpage with this graphic is to export an ipynb as an html file.
In [90]:
# Compute the mean dosage unit by year and buyer state
mean_dose = (annual.astype({'DOSAGE_UNIT': float}).groupby(['year', 'BUYER_STATE'])['DOSAGE_UNIT'].mean().reset_index(name='meandose'))

# Create the scatter plot using plotly express
fig = px.scatter(mean_dose, x='BUYER_STATE', y='meandose', color='year', title='Mean Opioid Pills by State and Year', labels={'meandose': 'Mean Opioid Pills','BUYER_STATE': 'State','year': 'Year'})

# Show the plot
fig.show()

fig.write_html('myplot.html')
<img src="myplot.html" />

  File "/var/folders/89/gtmbmmjd5kj5mpkf26_ykqzm0000gn/T/ipykernel_3346/2715923771.py", line 11
    <img src="myplot.html" />
    ^
SyntaxError: invalid syntax